/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.jdbc.dao.impl;
import com.jdbc.daos.Usertable1Dao;
import com.jdbc.domains.Usertable1;
import com.jdbc.driver.JdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author 虎
 */
public class Usertable1DaoImpl implements Usertable1Dao{
    Connection conn1 = null;
    Connection conn2 = null;
    Connection conn3 = null;
    ResultSet res =null;
    PreparedStatement st1 = null;
    PreparedStatement st2 = null;
    PreparedStatement st3 = null;
    
    @Override
    public void save(Usertable1 user) {
        try{
            
            conn1 = JdbcUtil.getConn();
            conn2 = JdbcUtil.getConn();
            /*
            st = conn.createStatement();
            String sql = "insert into tables values ('"+user.getUserID()+"','"+user.getPassword()+"','"+user.getName()+"','"+user.getSex()+"','"+user.getID()+"','"+user.getProvince()+"','"+user.getPhone()+"','"+user.getEmail()+"')";
            st.executeUpdate(sql);
            */
            String sql1 = "insert into table1 values (?,?,?,?,?,?,?,?)";
            st1 = conn1.prepareStatement(sql1);
            st1.setString(1,user.getJob_number1());
            st1.setInt(2,user.getPost_number1());
            st1.setDate(3,user.getBegin_time());
            st1.setDate(4,user.getEnd_time());
            st1.setDouble(5,user.getMonth_salary());
            st1.setDouble(6,user.getBonus());
            st1.setDouble(7,user.getFine());
            st1.setDouble(8,user.getTotal_salary());
            st1.executeUpdate();
            
            String sql2 = "insert into table2 values (?,?,?,?,?,?,?,?,?)";
            st2 = conn2.prepareStatement(sql2);
            st2.setString(1,user.getJob_number2());
            st2.setString(2,user.getEmployee_name());
            st2.setString(3,user.getSex());
            st2.setString(4,user.getID());
            st2.setDate(5,user.getBirthday());
            st2.setInt(6,user.getAge());
            st2.setString(7,user.getPhone());
            st2.setString(8,user.getProvince());
            st2.setString(9,user.getEmail());
            st2.executeUpdate();
            
            
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            JdbcUtil.close(conn1, st1, null);
            JdbcUtil.close(conn2, st2, null);
        }
    }

    @Override
    public void delete(String s,int i) {
        conn1 = JdbcUtil.getConn();
        conn2 = JdbcUtil.getConn();
        String sql1,sql2;
        if(i==1){
            sql1 = "delete from table1 where job_number1 = ?";
            sql2 = "delete from table2 where job_number2 = ?";
        }else{
            sql1 = "delete from table1 where job_number1 in( select job_number1 from table2,(select job_number1 from table1)as t1 where id = ? and job_number2 = t1.job_number1 )";
            sql2 = "delete from table2 where id = ?";
        }
        try {
            st1 = conn1.prepareStatement(sql1);
            st1.setString(1,s);
            st1.executeUpdate("set foreign_key_checks = 0");
            st1.executeUpdate();
            st1.executeUpdate("set foreign_key_checks = 1");
            st2 = conn2.prepareStatement(sql2);
            st2.setString(1,s);
            st2.executeUpdate("set foreign_key_checks = 0");
            st2.executeUpdate();
            st2.executeUpdate("set foreign_key_checks = 1");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(conn1, st1, null);
            JdbcUtil.close(conn2, st2, null);
        }
    }

    @Override
    public void update(Usertable1 user, int p) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public Usertable1 get(String job_number) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Usertable1> listAll(int i) {
        if(i==1){
            try{
                conn1 = JdbcUtil.getConn();
                String sql = "select * "
                        + "from table1,table2,table3 "
                        + "where table1.job_number1 = table2.job_number2 and table1.post_number1 = table3.post_number3";
                st1 = conn1.prepareStatement(sql);
                res = st1.executeQuery(sql);
                List<Usertable1> userList = new ArrayList<>();
                while(res.next()){
                    Usertable1 u1 = new Usertable1();
                    u1.setJob_number1(res.getString("job_number1"));
                    u1.setPost_number1(res.getInt("post_number1"));
                    u1.setBegin_time(res.getDate("begin_time"));
                    u1.setEnd_time(res.getDate("end_time"));
                    u1.setMonth_salary(res.getInt("month_salary"));
                    u1.setBonus(res.getInt("bonus"));
                    u1.setFine(res.getInt("fine"));
                    u1.setTotal_salary(res.getInt("total_salary"));
                
                    u1.setJob_number2(res.getString("job_number2"));
                    u1.setEmployee_name(res.getString("employee_name"));
                    u1.setSex(res.getString("sex"));
                    u1.setID(res.getString("ID"));
                    u1.setBirthday(res.getDate("birthday"));
                    u1.setAge(res.getInt("age"));
                    u1.setPhone(res.getString("phone"));
                    u1.setProvince(res.getString("province"));
                    u1.setEmail(res.getString("email"));
                
                    u1.setPost_number3(res.getInt("post_number3"));
                    u1.setPost(res.getString("post"));
                    userList.add(u1);
                }
                return userList;
            }catch(SQLException e){
                e.printStackTrace();
            }finally{
                JdbcUtil.close(conn1, st1, null);
            }
            return null;
        }else{
            try{
                conn1 = JdbcUtil.getConn();
                String sql = "select * from table3 ";
                st1 = conn1.prepareStatement(sql);
                res = st1.executeQuery(sql);
                List<Usertable1> userList = new ArrayList<>();
                while(res.next()){
                    Usertable1 u1 = new Usertable1();
                    u1.setJob_number1(null);
                    u1.setPost_number1(0);
                    u1.setBegin_time(null);
                    u1.setEnd_time(null);
                    u1.setMonth_salary(0);
                    u1.setBonus(0);
                    u1.setFine(0);
                    u1.setTotal_salary(0);
                
                    u1.setJob_number2(null);
                    u1.setEmployee_name(null);
                    u1.setSex(null);
                    u1.setID(null);
                    u1.setBirthday(null);
                    u1.setAge(0);
                    u1.setPhone(null);
                    u1.setProvince(null);
                    u1.setEmail(null);
                
                    u1.setPost_number3(res.getInt("post_number3"));
                    u1.setPost(res.getString("post"));
                    userList.add(u1);
                }
                return userList;
            }catch(SQLException e){
                e.printStackTrace();
            }finally{
                JdbcUtil.close(conn1, st1, null);
            }
            return null;
        }
    }
}